create external table jms_dm.dm_employee_operate_effective_dt 
(
  date_time date COMMENT ' 日期', 
  agent_code string COMMENT '代理区code', 
  agent_name string COMMENT '代理区name', 
  center_code string COMMENT '中心code', 
  center_name string COMMENT '中心名称', 
  institution_code string COMMENT '标准组织code', 
  institution_name string COMMENT '标准组织name', 
  institutiona_code string COMMENT '组织code', 
  institutiona_name string COMMENT '组织name', 
  schedule_code string COMMENT '班次code', 
  schedule_name string COMMENT '班次name', 
  operate_num int COMMENT '操作量', 
  formal_number decimal(16,2) COMMENT '自有员工操作数', 
  informal_number decimal(16,2) COMMENT '外包临时工人数', 
  effectiveness decimal(16,2) COMMENT '效能', 
  up_operate_num int COMMENT '上一日操作量', 
  up_formal_number decimal(16,2) COMMENT '上一日自有员工操作数', 
  up_informal_number decimal(16,2) COMMENT '上一日外包临时工人数', 
  up_effectiveness decimal(16,2) COMMENT '上一日效能', 
  org_type string COMMENT '维度：institutiona 班组，center中心 '
)
comment 'insert overwrite table jms_dm.dm_employee_operate_effective_dt'
PARTITIONED BY ( dt string COMMENT '时间分区')
stored as parquet
LOCATION '/dw/hive/jms_dm.db/external/dm_employee_operate_effective_dt'
TBLPROPERTIES (     'discover.partitions' = 'false',  'parquet.column.index.access' = 'true');

alter table jms_dm.dm_employee_operate_effective_dt add columns
(
      formal_hours   decimal(16,2) comment'正式工在岗时长'
     ,noformal_hours decimal(16,2) comment'临时工在岗时长'
     ,up_formal_hours   decimal(16,2) comment'上一天正式工在岗时长'
     ,up_noformal_hours decimal(16,2) comment'上一天临时工在岗时长'
)cascade;


alter table jms_dm.dm_employee_operate_effective_dt add columns
    (
         operate_bill_num int comment'操作量（票数）'
     ,up_operate_bill_num  int comment'上一天操作量（票数）'
     ,bill_effectiveness   decimal(16,2) comment'操作量（票数）效能'
     ,up_bill_effectiveness decimal(16,2) comment'上一天操作量（票数）效能'
)cascade;
